﻿using Hsp.Auto.Common.Data;
using Hsp.Auto.Common.Utility;
using Hsp.Auto.Interface;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;

namespace Hsp.Auto.MySql
{
    public class MySqlHelper : DataHelper, IDbHelper
    {
        #region 泛型方法查询实体数据

        /// <summary>
        /// 根据属性类型获取字段名称
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        internal static string GetFiledsByType(Type type)
        {
            return string.Join(",", type.GetProperties().Where(p => p.IsValidSqlField()).Select(p => $"`{p.GetColumnName()}` AS `{p.Name}`"));
        }

        /// <summary>
        /// 泛型方法查询数据实体
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="id">id</param>
        /// <returns></returns>
        public T SingleQuery<T>(int id)
        {
            try
            {
                return (T)Query<T>(id);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public T Query<T>(object id)
        {
            Type type = typeof(T);
            var strSql = $"SELECT {GetFiledsByType(type)} FROM `{AttributeExtend.GetTableName(type)}` WHERE (`Id`=@Id);";

            using MySqlConnection connection = new(connectionString);
            connection.Open();
            using MySqlCommand MySqlCommand = new(strSql, connection);
            MySqlCommand.Parameters.AddWithValue("@Id", id);
            MySqlDataReader reader = MySqlCommand.ExecuteReader();
            return reader.Read() ? (T)this.GetEntity(type, reader) : default;
        }

        /// <summary>
        /// 泛型方法查询数据实体
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="id">id</param>
        /// <returns></returns>
        public T SingleQuery<T>(string id)
        {
            try
            {
                return (T)Query<T>(id);
                //Type type = typeof(T);
                //var strSql = $"SELECT {GetFiledsByType(type)} FROM `{AttributeExtend.GetTableName(type)}` WHERE (`Id`=@Id);";

                //using MySqlConnection connection = new(connectionString);
                //connection.Open();
                //using MySqlCommand MySqlCommand = new(strSql, connection);
                //MySqlCommand.Parameters.AddWithValue("@Id", id);
                //MySqlDataReader reader = MySqlCommand.ExecuteReader();
                //return reader.Read() ? (T)this.GetEntity(type, reader) : default;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 泛型方法查询数据实体
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="id">id</param>
        /// <returns></returns>
        public T SingleQuery<T>(Guid id)
        {
            try
            {
                return (T)Query<T>(id);

                //Type type = typeof(T);
                //var strSql = $"SELECT {GetFiledsByType(type)} FROM `{AttributeExtend.GetTableName(type)}` WHERE (`Id`=@Id);";

                //using MySqlConnection connection = new(connectionString);
                //connection.Open();
                //using MySqlCommand MySqlCommand = new(strSql, connection);
                //MySqlCommand.Parameters.AddWithValue("@Id", id);
                //MySqlDataReader reader = MySqlCommand.ExecuteReader();
                //return reader.Read() ? (T)this.GetEntity(type, reader) : default;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        #endregion

        #region 泛型方法查询列表数据

        /// <summary>
        /// 泛型方法查询数据列表
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <returns></returns>
        public List<T> ListQuery<T>()
        {
            Type type = typeof(T);
            var strSql = $"SELECT {GetFiledsByType(type)} FROM `{AttributeExtend.GetTableName(type)}`;";
            return this.ListQuery<T>(strSql);
        }


        /// <summary>
        /// 泛型方法查询数据列表
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="strSql">数据库脚本</param>
        /// <returns></returns>
        public List<T> ListQuery<T>(string strSql)
        {
            var i = 0;
            try
            {
                List<T> list = null; // new List<T>();
                Type type = typeof(T); // User是关键字，因此要包成[User]
                using (MySqlConnection connection = new(connectionString))
                {
                    connection.Open();
                    using MySqlCommand MySqlCommand = new(strSql, connection);
                    using MySqlDataReader reader = MySqlCommand.ExecuteReader();

                    if (reader.HasRows)
                    {
                        list = new List<T>();
                    }
                    while (reader.Read()) // 开始读取下一条数据
                    {
                        object obj = Activator.CreateInstance(type);

                        foreach (var prop in type.GetProperties())
                        {
                            //var a = prop;
                            //var c = b.Name;
                            //var d = b.FullName;
                            var field = prop.GetColumnName();
                            var b = Type.GetType(prop.PropertyType.FullName); //属性类型

                            //if (field == "administrativeregions_city ")
                            //{
                            //    var e = field;
                            //}

                            //if (prop.Name == "ModifierId")
                            //{
                            //    var f = prop.Name;
                            //}

                            try
                            {
                                #region 容错处理

                                //if (!prop.PropertyType.IsPublic || !prop.CanWrite) continue;
                                if (!prop.IsValidSqlField()) continue;

                                object value = null;

                                try
                                {
                                    value = (reader[field] == DBNull.Value) ? null : reader[field];
                                }
                                catch
                                {
                                    continue;
                                }

                                //if (value == null) continue;

                                #endregion

                                if (prop.PropertyType.Name == "Guid")
                                {
                                    value = (reader[field] == DBNull.Value) ? default : new Guid(reader[field].ToString());
                                }

                                if (prop.PropertyType.Name == "Nullable`1")
                                {
                                    string fullName = prop.PropertyType.FullName;
                                    var isInt = fullName.Contains("System.Int32") || fullName.Contains("System.Int64");
                                    if (isInt) value = value == null ? 0 : Convert.ToInt32(value);
                                }

                                var type1 = prop.GetMethod.ReturnParameter.ParameterType.Name;
                                var type2 = prop.PropertyType.Name;

                                if (prop.GetMethod.ReturnParameter.ParameterType.Name == "Int32")
                                {
                                    value = Convert.ToInt32(value);
                                }

                                // System.String
                                if (field == "DefaultValue")
                                {
                                    var v = value;
                                    //var b = v;
                                }

                                if (prop.PropertyType.Name == "String")
                                {
                                    prop.SetValue(obj, value);
                                }
                                else
                                {
                                    prop.SetValue(obj, SqlNull(value));
                                }

                                //第五步：使用
                                //LogHelper.WriteLog(string.Format("***************************************"));
                                LogHelper.WriteLog(string.Format($"{i++:00}日志，泛型：{0}，属性：{1}，类型：{2}，类型1：{3}，值：{4}", typeof(T).GetType().Name, prop.Name, prop.PropertyType.Name, prop.GetMethod.ReturnParameter.ParameterType.Name, value));
                                //LogHelper.WriteLog(string.Format("日志，值：{0}", value));

                            }
                            catch (Exception ex)
                            {
                                var innerEx = ex.Message;
                                //第五步：使用
                                LogHelper.WriteLog(string.Format($"{i++:00}日志，泛型：{0}，属性：{1}，值：{2}，错误信息：{3}", typeof(T).GetType().Name, prop.Name, (reader[prop.Name] == DBNull.Value) ? null : reader[prop.Name], ex.Message));

                                throw;
                            }

                            #region 注释内容

                            //try
                            //{
                            //    if (!prop.PropertyType.IsPublic || !prop.CanWrite) continue;

                            //    prop.SetValue(obj, (reader[prop.Name] == DBNull.Value) ? null : reader[prop.Name]);

                            //    //if (prop.Name == "ModifierId")
                            //    //{
                            //    //    var ii = prop.Name;
                            //    //}

                            //    //Type pType = Type.GetType(prop.PropertyType.FullName); //属性类型
                            //    //var value = reader[prop.Name].ToString();

                            //    //var idx = pType.FullName.IndexOf("Nullable");

                            //    //if (pType.FullName.IndexOf("Nullable") == -1 && pType != null)
                            //    //{
                            //    //    prop.SetValue(obj, (reader[prop.Name] == DBNull.Value) ? null : reader[prop.Name]);

                            //    //    //if (pType.Name == "Guid")
                            //    //    //{
                            //    //    //    // Guid 类型要单独处理，Tli，20200714
                            //    //    //    Guid guid = string.IsNullOrEmpty(value) ? default(Guid) : new Guid(value);
                            //    //    //    type.GetProperty(prop.Name).SetValue(obj, guid, null); //赋值
                            //    //    //}
                            //    //    //else
                            //    //    //{
                            //    //    //    // (val1 == DBNull.Value) ? null : val1

                            //    //    //    var val = reader[prop.Name];
                            //    //    //    val = (val == DBNull.Value) ? null : val;
                            //    //    //    prop.SetValue(obj, val);

                            //    //    //    //type.GetProperty(prop.Name).SetValue(obj, Convert.ChangeType(value, pType), null); //赋值
                            //    //    //}
                            //    //}
                            //    //else
                            //    //{
                            //    //    Console.WriteLine($"{prop.Name}赋值{reader[prop.Name].ToString()}为空");
                            //    //}
                            //}
                            //catch (Exception ex)
                            //{
                            //    Console.WriteLine("{0}.{1}", this.GetType().Name, MethodBase.GetCurrentMethod().Name);
                            //    Console.WriteLine($"{prop.Name}赋值{reader[prop.Name].ToString()}出错：{ex.Message}");
                            //    //var msg = ex;
                            //}

                            #endregion
                        }

                        list.Add((T)obj);
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                //第五步：使用
                LogHelper.WriteLog(string.Format($"{i++:00}日志，List<T> ListQuery<T>执行出错, 泛型：{0}，脚本：{1}，错误信息：{0}.", typeof(T).GetType().Name, strSql, ex.Message));
                throw;
            }
        }

        #endregion

        #region SqlNull

        public static object SqlNull(object obj)
        {
            if (obj == null || obj.ToString() == "")
            {
                return DBNull.Value;
            }
            else
            {
                return obj;
            }
        }

        #endregion

        /// <summary>
        /// 获取实体数据
        /// </summary>
        /// <param name="type"></param>
        /// <param name="reader"></param>
        /// <returns></returns>
        private object GetEntity(Type type, MySqlDataReader reader)
        {
            object obj = Activator.CreateInstance(type)!;

            foreach (var prop in type.GetProperties())
            {
                if (!prop.IsValidSqlField()) continue;
                object value;
                var field = prop.GetColumnName();
                try
                {
                    value = (reader[field] == DBNull.Value) ? null : reader[field];
                }
                catch
                {
                    Console.WriteLine($"{field}读取数据错误！");
                    continue;
                }

                if (prop.PropertyType.Name == "Guid")
                {
                    value = (reader[field] == DBNull.Value) ? default : new Guid(reader[field].ToString());
                }

                if (prop.PropertyType.Name == "Nullable`1")
                {
                    string fullName = prop.PropertyType.FullName!;
                    var isInt = fullName.Contains("System.Int32") || fullName.Contains("System.Int64");
                    var isDouble = fullName.Contains("System.Double");
                    if (isInt) value = value == null ? 0 : Convert.ToInt32(value);
                    if (isDouble) value = value == null ? 0 : Convert.ToDouble(value);
                }

                if (prop.PropertyType.Name == "String")
                {
                    prop.SetValue(obj, value);
                }
                else
                {
                    prop.SetValue(obj, SqlNull(value));
                }
            }

            return obj;
        }

        #region 泛型数据库增删改

        /// <summary>
        /// 泛型数据库实体插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public int Add<T>(T t)
        {
            try
            {
                Type type = typeof(T); // User是关键字，因此要包成[User]

                // 添加的时候不更新包含“LastModif”的字段
                //var addProperties = type.GetProperties().Where(a => a.Name.IndexOf("LastModif") == -1);
                //var properties = addProperties.Where(a => a.Name != "Id"); // 也不包含主键字段

                //var fields = string.Join(',', properties.Select(p => '[' + p.Name + ']'));
                //var parameters = string.Join(',', properties.Select(p => '@' + p.Name));
                //var strSql = $"INSERT INTO [{type.Name}] ( {fields} ) VALUES ( {parameters} );";
                //IEnumerable<PropertyInfo> properties = type.GetProperties();
                //var strSql = AddGenericCache<T>.GetSqlString(out properties);

                var strSql = SqlCache<T>.InsertString;
                IEnumerable<PropertyInfo> properties = type.GetProperties();

                using MySqlConnection connection = new(connectionString);
                connection.Open();
                using MySqlCommand MySqlCommand = new(strSql, connection);
                foreach (PropertyInfo prop in properties)
                {
                    if (!prop.PropertyType.IsPublic || !prop.CanWrite) continue;
                    if (prop.PropertyType.IsInterface) continue;
                    if (!prop.PropertyType.IsSerializable) continue;

                    var field = prop.GetColumnName();

                    #region 字段允许Null值

                    //var isNullable = true; // 允许Null值
                    //if (prop.IsDefined(typeof(ColumnAttribute), true))
                    //{
                    //    foreach (ColumnAttribute column in prop.GetCustomAttributes(typeof(ColumnAttribute), true))
                    //    {
                    //        isNullable = column.Nullable;
                    //        System.Console.WriteLine($"{prop.Name}{column.Nullable}");
                    //    }
                    //}

                    #endregion

                    #region 注释

                    //if(prop.Name == "CreateTime")
                    //{
                    //    var p = prop.GetValue(t).ToString();
                    //    System.Console.WriteLine(p);

                    //    var aa = DateTime.Parse("1753/1/1 12:00:00");
                    //    System.Console.WriteLine(aa);
                    //}

                    //if (prop.Name == "F_Binary")
                    //{
                    //    var dataType = prop.PropertyType.FullName;

                    //    var type1 = "";
                    //}

                    //System.Console.WriteLine(prop.Name);

                    #endregion

                    object value = prop.GetValue(t);
                    Type piType = Type.GetType(prop.PropertyType.FullName); //属性类型
                    if (piType.Name == "Guid")
                    {
                        if (value == null) value = Guid.Empty;
                    }
                    if (piType.Name == "DateTime")
                    {
                        if (value.ToString() == "0001/1/1 0:00:00")
                        {
                            value = null;
                            //if (isNullable)
                            //{
                            //    value = null;
                            //}
                            //else
                            //{
                            //    value = DateTime.Parse("1753/1/1 12:00:00");
                            //}
                        }
                    }
                    MySqlCommand.Parameters.AddWithValue($"@{field}", SqlNull(value));

                }

                // 不能将值 NULL 插入列 'CreateTime'，表 'SampleDB.dbo.DemoTable'；列不允许有 Null 值。INSERT 失败。
                // 要注意的就是，这里的IsNullable，不是说你可以插入null值，而是指DBNull.Value值。
                //sqlCmd.Parameters[0].IsNullable = true;
                //sqlCmd.Parameters[1].IsNullable = true;

                return MySqlCommand.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);

                throw;
            }
        }

        /// <summary>
        /// 泛型数据库实体删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id">主键</param>
        /// <returns></returns>
        public int Delete<T>(int id)
        {
            try
            {
                Type type = typeof(T); // User是关键字，因此要包成[User]
                var strSql = $"DELETE FROM `{AttributeExtend.GetTableName(type)}` WHERE (`Id`=@Id);";

                using MySqlConnection connection = new(connectionString);
                connection.Open();
                using MySqlCommand MySqlCommand = new(strSql, connection);
                MySqlCommand.Parameters.AddWithValue("@Id", id);
                return MySqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 泛型数据库实体更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public int Update<T>(T t)
        {
            try
            {
                Type type = typeof(T); // User是关键字，因此要包成[User]

                //// 修改的时候不更新包含“Creat”的字段
                //var properties = type.GetProperties().Where(a => a.Name.IndexOf("Creat") == -1);
                //var editproperties = properties.Where(a => a.Name != "Id"); // 可编辑字段
                //var fieldValues = string.Join(',', editproperties.Select(p => "[" + p.Name + "]=@" + p.Name));
                //var strSql = $"UPDATE [{type.Name}] SET {fieldValues} WHERE (Id = @Id);";

                IEnumerable<System.Reflection.PropertyInfo> properties = type.GetProperties();
                var strSql = SqlCache<T>.UpdateString; // 不能用公共的，不同的数据库类型`[不同。

                using MySqlConnection connection = new(connectionString);
                connection.Open();
                using MySqlCommand MySqlCommand = new(strSql, connection);
                foreach (var prop in properties)
                {
                    var value = prop.GetValue(t);

                    MySqlCommand.Parameters.AddWithValue($"@{prop.GetColumnName()}", SqlNull(value));
                }

                return MySqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public T SingleWhereQuery<T>(string sqlwhere)
        {
            throw new NotImplementedException();
        }

        public int DeleteByWhereSql<T>(string sqlwhere)
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}
